﻿
namespace SistemaLogistica.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using SistemaLogistica.Business.Entities;

    public class UbigeoData : BaseDataAccess
    {
        private UbigeoData() { }

        private readonly static UbigeoData instancia = new UbigeoData();

        public static UbigeoData Instancia
        {
            get { return instancia; }
        }

        public List<Ubigeo> Listar()
        {
            List<Ubigeo> ubigeos = new List<Ubigeo>();
            DbCommand command = db.GetStoredProcCommand("GetAllFromUbigeo");

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    ubigeos.Add(new Ubigeo
                    {
                        Departamento = dr.GetString(dr.GetOrdinal("Departamento")),
                        Distrito = dr.GetString(dr.GetOrdinal("Distrito")),
                        IdDepartamento = dr.GetInt32(dr.GetOrdinal("IdDepartamento")),
                        IdDistrito = dr.GetInt32(dr.GetOrdinal("IdDistrito")),
                        IdProvincia = dr.GetInt32(dr.GetOrdinal("IdProvincia")),
                        IdUbigeo = dr.GetInt32(dr.GetOrdinal("IdUbigeo")),
                        Provincia = dr.GetString(dr.GetOrdinal("Provincia"))
                    });
                }
            }
            return ubigeos;
        }

        public List<Ubigeo> ListarDepartamentos()
        {
            List<Ubigeo> listaDepartamentos = new List<Ubigeo>();
            DbCommand command = db.GetStoredProcCommand("GetAllDepartamentos");

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    listaDepartamentos.Add(new Ubigeo
                    {
                        Departamento = dr.GetString(dr.GetOrdinal("Departamento")),
                        IdDepartamento = dr.GetInt32(dr.GetOrdinal("IdDepartamento"))
                    });
                }
            }
            return listaDepartamentos;
        }

        public List<Ubigeo> ListarProvincias(int departamentoID)
        {
            List<Ubigeo> listaProveincias = new List<Ubigeo>();
            DbCommand command = db.GetStoredProcCommand("GetProvinciasByDepartamentoID");
            db.AddInParameter(command, "intDepartamentoID", DbType.Int32, departamentoID);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    listaProveincias.Add(new Ubigeo
                    {
                        IdProvincia = dr.GetInt32(dr.GetOrdinal("IdProvincia")),
                        Provincia = dr.GetString(dr.GetOrdinal("Provincia"))
                    });
                }
            }
            return listaProveincias;
        }

        public List<Ubigeo> ListarDistritos(int departamentoID, int provinciaID)
        {
            List<Ubigeo> listaDistritos = new List<Ubigeo>();
            DbCommand command = db.GetStoredProcCommand("GetDistritosByDepaIDAndProviID");
            db.AddInParameter(command, "intDepartamentoID", DbType.Int32, departamentoID);
            db.AddInParameter(command, "intProvinciaID", DbType.Int32, provinciaID);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    listaDistritos.Add(new Ubigeo
                    {
                        IdUbigeo = dr.GetInt32(dr.GetOrdinal("IdUbigeo")),
                        IdDistrito = dr.GetInt32(dr.GetOrdinal("IdDistrito")),
                        Distrito = dr.GetString(dr.GetOrdinal("Distrito"))
                    });
                }
            }
            return listaDistritos;
        }

        public Ubigeo ObtenerPorID(int ubigeoID)
        {
            Ubigeo ubigeo = null;
            DbCommand command = db.GetStoredProcCommand("GetUbigeoByIdUbigeo");
            db.AddInParameter(command, "idUbigeo", DbType.Int32, ubigeoID);

            using (IDataReader dr = db.ExecuteReader(command))
            {
                while (dr.Read())
                {
                    ubigeo = new Ubigeo
                    {
                        Departamento = dr.GetString(dr.GetOrdinal("Departamento")),
                        Distrito = dr.GetString(dr.GetOrdinal("Distrito")),
                        IdDepartamento = dr.GetInt32(dr.GetOrdinal("IdDepartamento")),
                        IdDistrito = dr.GetInt32(dr.GetOrdinal("IdDistrito")),
                        IdProvincia = dr.GetInt32(dr.GetOrdinal("IdProvincia")),
                        IdUbigeo = dr.GetInt32(dr.GetOrdinal("IdUbigeo")),
                        Provincia = dr.GetString(dr.GetOrdinal("Provincia"))
                    };
                }
            }
            return ubigeo;
        }

        public int ListarIDPorDPD(int departamentoID, int provinciaID, int distritoID)
        {
            int ubigeoID = 0;
            DbCommand command = db.GetStoredProcCommand("GetUbigeoIDByDPD");
            db.AddOutParameter(command, "intUbigeoID", DbType.Int32, 11);
            db.AddInParameter(command, "intDepartamentoID", DbType.Int32, departamentoID);
            db.AddInParameter(command, "intProvinciaID", DbType.Int32, provinciaID);
            db.AddInParameter(command, "intDistritoID", DbType.Int32, distritoID);
            db.ExecuteNonQuery(command);
            ubigeoID = Convert.ToInt32(db.GetParameterValue(command, "intUbigeoID"));
            return ubigeoID;
        }
    }
}
